import pandas as pd
import numpy as np

# 读取数据
input_file = 'data_clean.csv'
output_file = 'data_analysis_new.js'
df = pd.read_csv(input_file, encoding='utf-8')

# 数据预处理
def to_num(col):
    return pd.to_numeric(df[col], errors='coerce')
for col in ['fields.comment', 'fields.discountPrice', 'fields.price', 'fields.itemTotalScore', 'fields.sold365', 'fields.soldRecentNum', 'fields.latitude', 'fields.longitude']:
    df[col] = to_num(col)
df['fields.comment'] = df['fields.comment'].fillna(0)
df['fields.sold365'] = df['fields.sold365'].fillna(0)
df['fields.soldRecentNum'] = df['fields.soldRecentNum'].fillna(0)
df['fields.itemTotalScore'] = df['fields.itemTotalScore'].fillna(0)
df['fields.discountPrice'] = df['fields.discountPrice'].fillna(df['fields.price'])
df['fields.discountPrice'] = df['fields.discountPrice'].fillna(0)

# 过滤掉订单数和评论数均为0的数据
df = df[~(df['fields.comment'] == 0)]

df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')

# 5月15日-6月15日销量占比最高的城市词云
mask = (df['order_date'] >= '2023-05-15') & (df['order_date'] <= '2023-06-15')
may_june = df[mask]
total_sales = df.groupby('city')['fields.sold365'].sum()
period_sales = may_june.groupby('city')['fields.sold365'].sum()
sales_ratio = (period_sales / total_sales).dropna().sort_values(ascending=False).head(20)
items = list(sales_ratio.items())

with open(output_file, 'w', encoding='utf-8') as f:
    # 1. 热门景点Top10
    hot_spots = df.groupby('fields.title')['fields.sold365'].sum().sort_values(ascending=False).head(10)
    items = list(hot_spots.items())
    f.write('var hotSpots = [\n')
    for i, (name, value) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{name}", value: {int(value)}}}{comma}\n')
    f.write('];\n\n')

    # 2. 订单数与评论数散点图
    order_comment = df[['fields.sold365', 'fields.comment']].dropna()
    items = list(order_comment.iterrows())
    f.write('var orderCommentScatter = [\n')
    for i, (_, row) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{order: {int(row["fields.sold365"])} , comment: {int(row["fields.comment"])} }}{comma}\n')
    f.write('];\n\n')

    # 3. 用户评分分布
    score_bins = [0, 3.5, 4.5, 5.1]
    score_labels = ['低于3.5', '3.5-4.5', '高于4.5']
    df['score_group'] = pd.cut(df['fields.itemTotalScore'], bins=score_bins, labels=score_labels, right=False)
    score_dist = df['score_group'].value_counts().sort_index()
    items = list(score_dist.items())
    f.write('var scoreDist = [\n')
    for i, (label, value) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{range: "{label}", value: {int(value)}}}{comma}\n')
    f.write('];\n\n')

    # 4. 评分与订单数散点图
    score_order = df[['fields.itemTotalScore', 'fields.sold365']].dropna()
    items = list(score_order.iterrows())
    f.write('var scoreOrderScatter = [\n')
    for i, (_, row) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{score: {row["fields.itemTotalScore"]}, order: {int(row["fields.sold365"])} }}{comma}\n')
    f.write('];\n\n')

    # 5. 价格分布
    price_dist = df['fields.discountPrice'].dropna()
    hist, bin_edges = np.histogram(price_dist, bins=10, range=(0, 2640))
    f.write('var priceDist = [\n')
    for i in range(len(hist)):
        comma = ',' if i < len(hist) - 1 else ''
        f.write(f'  {{range: "{bin_edges[i]:.1f}-{bin_edges[i+1]:.1f}", value: {int(hist[i])}}}{comma}\n')
    f.write('];\n\n')

    # 6. 价格与订单数散点图
    price_order = df[['fields.discountPrice', 'fields.sold365']].dropna()
    items = list(price_order.iterrows())
    f.write('var priceOrderScatter = [\n')
    for i, (_, row) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{price: {row["fields.discountPrice"]}, order: {int(row["fields.sold365"])} }}{comma}\n')
    f.write('];\n\n')

    # 7. 性价比Top10
    df['性价比'] = df['fields.itemTotalScore'] / (df['fields.discountPrice'].replace(0, np.nan))
    best_value = df[['fields.title', '性价比']].dropna().sort_values('性价比', ascending=False).head(10)
    items = list(best_value.iterrows())
    f.write('var bestValueSpots = [\n')
    for i, (_, row) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{row["fields.title"]}", value: {row["性价比"]:.2f}}}{comma}\n')
    f.write('];\n\n')

    # 8. 高评论数景点Top5
    top_comment = df[['fields.title', 'fields.comment', 'fields.sold365', 'fields.itemTotalScore']].dropna().sort_values('fields.comment', ascending=False).head(5)
    items = list(top_comment.iterrows())
    f.write('var topCommentSpots = [\n')
    for i, (_, row) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{row["fields.title"]}", comment: {int(row["fields.comment"])}, order: {int(row["fields.sold365"])}, score: {row["fields.itemTotalScore"]} }}{comma}\n')
    f.write('];\n\n')

    # 9. 高评分高订单景点
    high_score_order = df[(df['fields.itemTotalScore'] >= 4.5) & (df['fields.sold365'] >= 100)]
    items = list(high_score_order.iterrows())[:10]
    f.write('var highScoreOrderSpots = [\n')
    for i, (_, row) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{row["fields.title"]}", score: {row["fields.itemTotalScore"]}, order: {int(row["fields.sold365"])} }}{comma}\n')
    f.write('];\n\n')

    # 10. 低评分低订单景点
    low_score_order = df[(df['fields.itemTotalScore'] < 3.5) & (df['fields.sold365'] < 10)]
    items = list(low_score_order.iterrows())[:10]
    f.write('var lowScoreOrderSpots = [\n')
    for i, (_, row) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{row["fields.title"]}", score: {row["fields.itemTotalScore"]}, order: {int(row["fields.sold365"])} }}{comma}\n')
    f.write('];\n\n')

    # 11. 地图可视化数据
    map_data = df[['fields.title', 'fields.latitude', 'fields.longitude', 'fields.sold365', 'fields.itemTotalScore']].dropna()
    items = list(map_data.iterrows())
    f.write('var mapData = [\n')
    for i, (_, row) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{row["fields.title"]}", lat: {row["fields.latitude"]}, lng: {row["fields.longitude"]}, order: {int(row["fields.sold365"])} , score: {row["fields.itemTotalScore"]} }}{comma}\n')
    f.write('];\n')

    # 最近一个月售出门票数量Top10的城市
    recent_city = df.groupby('city')['fields.soldRecentNum'].sum().sort_values(ascending=False).head(10)
    items = list(recent_city.items())
    f.write('var recentMonthCityTop10 = [\n')
    for i, (name, value) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{name}", value: {int(value)}}}{comma}\n')
    f.write('];\n\n')

    # 词云：按城市景点数量统计
    city_count = df['city'].value_counts().head(20)
    items = list(city_count.items())
    f.write('var cityScoreWordCloud = [\n')
    for i, (name, value) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{name}", value: {int(value)}}}{comma}\n')
    f.write('];\n\n')

    # 每个城市的景点数量
    city_count = df['city'].value_counts()
    items = list(city_count.items())
    f.write('var citySpotCount = [\n')
    for i, (name, value) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{name}", value: {int(value)}}}{comma}\n')
    f.write('];\n\n')

    # 用city_data.csv建立城市到省份的映射，统计每个省份的景点数量
    city_map = pd.read_csv('city_data.csv', encoding='utf-8')
    city_to_province = dict(zip(city_map['city'], city_map['province']))
    df['mapped_province'] = df['city'].map(city_to_province)
    province_count = df['mapped_province'].value_counts()
    items = list(province_count.items())
    f.write('var provinceSpotCount = [\n')
    for i, (name, value) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{name}", value: {int(value)}}}{comma}\n')
    f.write('];\n\n')

    # 5月15日-6月15日销量占比最高的城市词云
    mask = (df['order_date'] >= '2023-05-15') & (df['order_date'] <= '2023-06-15')
    may_june = df[mask]
    total_sales = df.groupby('city')['fields.sold365'].sum()
    period_sales = may_june.groupby('city')['fields.sold365'].sum()
    sales_ratio = (period_sales / total_sales).dropna().sort_values(ascending=False).head(20)
    items = list(sales_ratio.items())
    f.write('var cityMayJuneWordCloud = [\n')
    for i, (name, value) in enumerate(items):
        comma = ',' if i < len(items) - 1 else ''
        f.write(f'  {{name: "{name}", value: {value*100:.2f}}}{comma}\n')
    f.write('];\n\n')

print(f'分析脚本已生成 {output_file}，可直接在前端js中引用。') 